package com.linlang.grasharepro.dao;

import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.linlang.grasharepro.model.NewMessageDomain;
import com.linlang.grasharepro.utils.DataBaseOpenHelper;

import java.util.ArrayList;

/**
 * Created by lenovo on 2016/8/3.
 */
public class NewMessageDB {
    public static final String SQLITE_TABLE = "new_message";

    public static final String PK = "_id";
    public static final String FROM_USER = "fromUser";
    public static final String TO_USER = "toUser";
    public static final String TIME = "time";
    public static final String TYPE = "type";
    public static final String CONTENT = "content";
    public static final String FLAG = "flag";
    public static final String IMAGE = "image";

    private static final String TAG = "NewMessageDbAdapter";
    private DatabaseHelper databaseHelper;
    private SQLiteDatabase sqLiteDatabase;
    private final Context context;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {

            super(context, DataBaseOpenHelper.DATABASE_NAME, null, DataBaseOpenHelper.DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
            onCreate(db);
        }
    }

    public NewMessageDB(Context context) {
        this.context = context;
    }

    public NewMessageDB open() throws SQLException {
        databaseHelper = new DatabaseHelper(context);
        sqLiteDatabase = databaseHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        if (databaseHelper != null) {
            databaseHelper.close();
        }
    }

    /**
     * 新增
     *
     * @param newMessageDomain
     * @return
     */
    public int insertNewMessage(NewMessageDomain newMessageDomain) {
        long createResult = 0;
        int strid = 0;
        ContentValues initialValues = new ContentValues();
        initialValues.put(FROM_USER, newMessageDomain.getFromUser());
        initialValues.put(TO_USER, newMessageDomain.getToUser());
        initialValues.put(TIME, newMessageDomain.getTime());
        initialValues.put(TYPE, newMessageDomain.getType());
        initialValues.put(CONTENT, newMessageDomain.getContent());
        initialValues.put(FLAG, newMessageDomain.getFlag());
        initialValues.put(IMAGE, newMessageDomain.getImage());
        try {
            createResult = sqLiteDatabase.insert(SQLITE_TABLE, null, initialValues);

            Cursor cursor = sqLiteDatabase.rawQuery("select last_insert_rowid() from " + SQLITE_TABLE, null);
            if (cursor.moveToFirst())
                strid = cursor.getInt(0);

        } catch (Exception e) {
            // TODO: handle exception
        }
        return strid;
    }

    /**
     * 删除表的全部字段数据
     *
     * @return
     */
    public boolean deleteAllMessage() {
        int doneDelete = 0;
        try {
            doneDelete = sqLiteDatabase.delete(SQLITE_TABLE, null, null);
            Log.w(TAG, Integer.toString(doneDelete));
            Log.e("doneDelete", doneDelete + "");
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return doneDelete > 0;
    }

    /**
     * 根据id删除
     *
     * @param id
     * @return
     */
    public boolean deleteById(String id) {
        int isDelete;
        String[] ids;
        ids = new String[]{id};
        isDelete = sqLiteDatabase.delete(SQLITE_TABLE, PK + "=?", ids);
        Log.e("deleteTicket", "isDelete:" + isDelete + "||" + "ticketID="
                + id);
        return isDelete > 0;
    }

    /**
     * 获取表中的所有字段
     *
     * @return
     */
    public ArrayList<NewMessageDomain> getMyNewMessage(String userName) {

        ArrayList<NewMessageDomain> newMessageDomains = new ArrayList<NewMessageDomain>();
        Cursor mCursor = null;
        mCursor = sqLiteDatabase.query(SQLITE_TABLE, null, TO_USER + "=?", new String[]{userName}, TYPE, null, TIME);
        if (mCursor.moveToFirst()) {
            do {
                NewMessageDomain newMessageDomain = new NewMessageDomain();
                newMessageDomain.set_id(PK);
                newMessageDomain.setFromUser(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(FROM_USER)));
                newMessageDomain.setToUser(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TO_USER)));
                newMessageDomain.setTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME)));
                newMessageDomain.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                newMessageDomain.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                newMessageDomain.setFlag(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(FLAG)));
                newMessageDomain.setImage(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMAGE)));
                newMessageDomains.add(newMessageDomain);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return newMessageDomains;
    }

    /**
     * 获取我与某人的私信
     *
     * @return
     */
    public ArrayList<NewMessageDomain> getMyprivateLetter(String myName, String toUser) {

        ArrayList<NewMessageDomain> newMessageDomains = new ArrayList<NewMessageDomain>();

//        String sql = "select "
//                + "t1." + PK
//                + ", t1." + FROM_USER
//                + ", t1." + TO_USER
//                + ", t1." + TIME
//                + ", t1." + TYPE
//                + ", t1." + CONTENT
//                + ", t1." + FLAG
//                + ", t1." + IMAGE
//                + " from "
//                + SQLITE_TABLE + " t1 where t1." + FROM_USER + "='" + myName + "'"
//                + " and t1." + TO_USER + "='" + toUser + "'"
//                + " and t1." + TYPE + "='1'"
//                + " union all select "
//                + " t2." + PK
//                + ", t2." + FROM_USER
//                + ", t2." + TO_USER
//                + ", t2." + TIME
//                + ", t2." + TYPE
//                + ", t2." + CONTENT
//                + ", t2." + FLAG
//                + ", t2." + IMAGE
//                + " from "
//                + SQLITE_TABLE + " t2 where t2." + FROM_USER + "='" + toUser + "'"
//                + " and t2." + TO_USER + "='" + myName + "'"
//                + " and t2." + TYPE + "='1'";
        String sql = "select * from (select * from "
                + SQLITE_TABLE + " t1 where t1." + FROM_USER + "='" + myName + "'"
                + " and t1." + TO_USER + "='" + toUser + "'"
                + " and t1." + TYPE + "='1'"
                + " union all select * from "
                + SQLITE_TABLE + " t2 where t2." + FROM_USER + "='" + toUser + "'"
                + " and t2." + TO_USER + "='" + myName + "'"
                + " and t2." + TYPE + "='1') t order by t." + TIME;


        Cursor mCursor = null;
//        mCursor = sqLiteDatabase.query(SQLITE_TABLE, null, FROM_USER + "=? and " + TO_USER + "=? and " + TYPE + "='1'",
//                new String[]{myName, toUser}, null, null, null);

        mCursor = sqLiteDatabase.rawQuery(sql, null);
        if (mCursor.moveToFirst()) {
            do {
                NewMessageDomain newMessageDomain = new NewMessageDomain();
                newMessageDomain.set_id(PK);
                newMessageDomain.setFromUser(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(FROM_USER)));
                newMessageDomain.setToUser(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TO_USER)));
                newMessageDomain.setTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME)));
                newMessageDomain.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                newMessageDomain.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                newMessageDomain.setFlag(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(FLAG)));
                newMessageDomain.setImage(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMAGE)));
                newMessageDomains.add(newMessageDomain);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return newMessageDomains;
    }
}
